package com.sep.team4.data;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * This class is used to get information from and store information into the
 * database. This class is a singleton and can't be initialized in other
 * classes. The only to get instances is by using the getInstance() method.
 * 
 * @author Mitchell
 * 
 */
public class DatabaseManager extends SQLiteOpenHelper {

	// An instance of this class;
	private static DatabaseManager dbManager;

	private SQLiteDatabase db;

	/**
	 * The constructor.
	 */
	private DatabaseManager(Context context) {
		super(context, "Sep1_App_Database", null, 1);
		this.db = getWritableDatabase();
		db.execSQL("drop table Course");
		this.onCreate(db);
	}

	public void clearAll() {
		db.execSQL("drop table Test");
		db.execSQL("create table if not exists Test ( id integer primary key autoincrement, "
				+ "name text, courseID int, weight int, lowest integer)");
		db.execSQL("delete from Course");
		db.execSQL("delete from Grade");
	}

	/**
	 * Returns an instance of this class.
	 * 
	 * @param context
	 *            A context instance needed to start the database, this is
	 *            mostly the Activity that called this method.
	 * @return
	 */
	public static DatabaseManager getInstance(Context context) {
		if (dbManager == null) {
			dbManager = new DatabaseManager(context);
		}
		return dbManager;
	}

	/**
	 * Returns an instance of this class. This method doesn't need a context
	 * instance, however DatabaseManager.getInstance(Context context) needs to
	 * be called before using this method, otherwise it will result in a
	 * NullPointerException.
	 * 
	 * @return An instance of this class.
	 */
	public static DatabaseManager getInstance() {
		return dbManager;
	}

	/**
	 * Returns an Array containing all the grades who's testID equals the testID
	 * parameter.
	 * 
	 * @param testID
	 *            The ID of the test which's grades have to be obtained from the
	 *            database.
	 * @return All the grades from a test.
	 */
	public ArrayList<Grade> getGrades(int testID) {
		Cursor cursor = db.query("Grade", new String[] { "id", "value",
				"attempt", "testID", "insertDate" }, "testID =" + testID, null,
				null, null, null, null);
		ArrayList<Grade> grades = new ArrayList<Grade>();
		cursor.moveToFirst();
		if (!cursor.isAfterLast()) {
			do {
				Grade grade = new Grade(cursor.getDouble(1), cursor.getInt(2),
						cursor.getString(4));
				grades.add(grade);
			} while (cursor.moveToNext());
		}
		cursor.close();
		return grades;
	}

	/**
	 * Returns an Array containing all the test that belong to the course who's
	 * name equals the value of the name parameter.
	 * 
	 * @param name
	 *            The name of the Course who's tests have to be obtained.
	 * @return All the tests from a course.
	 */
	public ArrayList<Test> getTests(int courseID) {
		Cursor cursor = db.query("Test", new String[] { "id", "name",
				"courseID", "weight", "lowest" }, "courseID =" + courseID,
				null, null, null, null, null);
		ArrayList<Test> tests = new ArrayList<Test>();
		cursor.moveToFirst();
		if (!cursor.isAfterLast()) {
			do {
				Test test = new Test(cursor.getString(1), cursor.getInt(3),
						cursor.getInt(0), cursor.getDouble(4));
				tests.add(test);
			} while (cursor.moveToNext());
		}
		cursor.close();
		return tests;
	}

	/**
	 * Returns a Course object which's course code equals the value of the code
	 * parameter.
	 * 
	 * @param code
	 *            The code of the course that has to be obtained from the
	 *            database.
	 * @return
	 */
	public Course getCourseByCode(String code) {
		Cursor cursor = db.query("Course", new String[] { "id", "name",
				"semester", "code", "SBU" }, "code =" + "'" + code + "'", null,
				null, null, null, null);
		Course course = null;
		cursor.moveToFirst();
		if (!cursor.isAfterLast()) {
			course = new Course(cursor.getString(1), cursor.getString(2),
					cursor.getInt(0), cursor.getString(3), cursor.getInt(4));
		}
		cursor.close();
		return course;
	}

	/**
	 * Returns a Course object which's course name equals the value of the name
	 * parameter.
	 * 
	 * @param name
	 *            The name of the course that has to be obtained from the
	 *            database.
	 * @return
	 */
	public Course getCourseByName(String name) {
		Cursor cursor = db.query("Course", new String[] { "id", "name",
				"semester", "code", "SBU" }, "name = " + "'" + name + "'",
				null, null, null, null, null);
		Course course = null;
		cursor.moveToFirst();
		if (!cursor.isAfterLast()) {
			course = new Course(cursor.getString(1), cursor.getString(2),
					cursor.getInt(0), cursor.getString(3), cursor.getInt(4));
		}
		cursor.close();
		return course;
	}

	/**
	 * Returns an Array of Course objects which's semester equals the value of
	 * the semester parameter.
	 * 
	 * @param code
	 *            The code of the course that has to be obtained from the
	 *            database.
	 * @return
	 */
	public ArrayList<Course> getCoursesBySemester(String semester) {
		Cursor cursor = db.query("Course", new String[] { "id", "name",
				"semester", "code", "SBU" }, "semester =" + "'" + semester
				+ "'", null, null, null, null, null);
		ArrayList<Course> courses = new ArrayList<Course>();
		cursor.moveToFirst();
		if (!cursor.isAfterLast()) {
			do {
				Course course = new Course(cursor.getString(1),
						cursor.getString(2), cursor.getInt(0),
						cursor.getString(3), cursor.getInt(4));
				courses.add(course);
			} while (cursor.moveToNext());
		}
		cursor.close();
		return courses;
	}

	/**
	 * Returns an ArrayList of the semesters the Student has attended, The
	 * ArrayList will be ordered from last semester to first
	 * 
	 * @return
	 */
	public ArrayList<String> getAttendedSemesters() {
		ArrayList<String> semesters = new ArrayList<String>();
		Cursor cursor = db.query("Course",
				new String[] { "DISTINCT semester" }, null, null, null, null,
				"semester desc");
		cursor.moveToFirst();
		if (!cursor.isAfterLast()) {
			do {
				semesters.add(cursor.getString(0));
			} while (cursor.moveToNext());
		}
		cursor.close();
		return semesters;
	}

	/**
	 * Adds a new grade to the database.
	 * 
	 * @param grade
	 *            The Grade object that contains the information had needs to be
	 *            stored in the database.
	 * @param testID
	 *            The id of the test the grade belongs too.
	 */
	public void addGrade(Grade grade, int testID) {
		// this is a key value pair holder used by android's SQLite functions
		ContentValues values = new ContentValues();
		values.put("value", grade.getValue());
		values.put("attempt", grade.getAttempt());
		values.put("testID", testID);
		values.put("insertDate", grade.getDate());
		// ask the database object to insert the new data
		try {
			db.insert("Grade", null, values);
		} catch (Exception e) {
			Log.e("MDG", "add grade failure");
		}
	}

	/**
	 * Adds a new test to the database.
	 * 
	 * @param grade
	 *            The Test object that contains the information had needs to be
	 *            stored in the database.
	 * @param courseID
	 *            The id of the course the test belongs too.
	 */
	public void addTest(Test test, int courseID) {
		// this is a key value pair holder used by android's SQLite functions
		ContentValues values = new ContentValues();
		values.put("name", test.getName());
		values.put("courseID", courseID);
		values.put("weight", test.getWeight());
		values.put("lowest", test.getLowestAllowedGrade());
		// ask the database object to insert the new data
		try {
			db.insert("Test", null, values);
		} catch (Exception e) {
			Log.e("MDG", "add grade failure");
		}
	}

	/**
	 * Adds a new course to the database.
	 * 
	 * @param grade
	 *            The Course object that contains the information had needs to
	 *            be stored in the database.
	 */
	public void addCourse(Course course) {
		// this is a key value pair holder used by android's SQLite functions
		ContentValues values = new ContentValues();
		values.put("name", course.getName());
		values.put("semester", course.getSemester());
		values.put("code", course.getCode());
		values.put("SBU", course.getSBU());
		// ask the database object to insert the new data
		try {
			db.insert("Course", null, values);
		} catch (Exception e) {
			Log.e("MDG", "add grade failure");
		}
	}

	/**
	 * Returns the next available id in the test table.
	 * 
	 * @return The next available id in the test table.
	 */
	public int getNextAvailableTestID() {
		int id = 0;
		Cursor cursor = db.query("Test", new String[] { "id" }, null, null,
				null, null, null, null);
		id = cursor.getCount() + 1;
		cursor.close();
		return id;
	}

	/**
	 * Returns the next available id in the course table.
	 * 
	 * @return The next available id in the course table.
	 */
	public int getNextAvailableCourseID() {
		int id = 0;
		Cursor cursor = db.query("Course", new String[] { "id" }, null, null,
				null, null, null, null);
		id = cursor.getCount() + 1;
		cursor.close();
		return id;
	}

	@Override
	/**
	 * This method is called when an instance of this class is created. It creates all the tables 
	 * in the database if they don't exist yet.
	 */
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("create table if not exists Course ( id integer primary key autoincrement, "
				+ "name text, semester text, code text, SBU integer)");
		db.execSQL("create table if not exists Test ( id integer primary key autoincrement, "
				+ "name text, courseID int, weight int, lowest integer)");
		db.execSQL("create table if not exists Grade ( id integer primary key autoincrement, "
				+ "value integer, attempt integer, testID integer, insertDate text)");
	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// Not required.
	}
}
